﻿Imports System.Data.SqlClient

Public Class ChangeZR
    Private user As String
    Private userSite As String
    Private cn As CnData
    Private da As SqlDataAdapter
    Private ds As DataSet
    Private cmd As SqlCommand
    Private dtr As SqlDataReader
    Private choix As String
    Private F_load As Boolean

    Public Sub New(ByVal user As String, ByRef cn As CnData, ByVal userSite As String)
        ' Cet appel est requis par le concepteur.
        InitializeComponent()
        ' Ajoutez une initialisation quelconque après l'appel InitializeComponent().
        Me.user = user
        Me.cn = cn
        Me.userSite = userSite
        Me.F_load = True
    End Sub


    Private Sub ChangeZR_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.load
        cn.Open()
        da = New SqlDataAdapter("SELECT Distinct TypeFlux FROM ZR WHERE Actif = 1 ORDER BY TypeFlux", cn.getCn)
        ds = New DataSet
        da.Fill(ds, "Type")
        With TypeF
            .DataSource = ds.Tables("Type")
            .DisplayMember = "TypeFlux"
            .ValueMember = "TypeFlux"
        End With

        da = New SqlDataAdapter("SELECT Distinct Site,isnull(NomDoc,'Tous') as NomDoc FROM ZR left join Sites on NumSite = ZR.Site WHERE TypeFlux='" & TypeF.SelectedValue & "' AND  ZR.Actif = 1 AND Sites.Actif = 1", cn.getCn)
        ds = New DataSet
        da.Fill(ds, "Sites")
        With SiteZR
            .DataSource = ds.Tables("Sites")
            .DisplayMember = "NomDoc"
            .ValueMember = "Site"
            .SelectedIndex = 0
        End With

        da = New SqlDataAdapter("SELECT '00' as NumSite,'Tous' as NomDoc UNION SELECT NumSite,NomDoc FROM Sites WHERE Sites.Actif = 1", cn.getCn)
        ds = New DataSet
        da.Fill(ds, "NSites")
        With NewSite
            .DataSource = ds.Tables("NSites")
            .DisplayMember = "NomDoc"
            .ValueMember = "NumSite"
            .SelectedValue = "00"
        End With

        cmd = New SqlCommand("SELECT TOP 1 * from ZR WHERE TypeFlux='" & TypeF.SelectedValue & "' AND Actif = 1", cn.getCn)
        'val = cmd.ExecuteScalar
        dtr = cmd.ExecuteReader()
        If dtr.Read() Then

            CR.Text = dtr(dtr("Choix").ToString).ToString
            choix = dtr("Choix").ToString
            If String.IsNullOrEmpty(dtr("INFO2").ToString) Then
                Panel2.Visible = False
            Else
                Panel2.Visible = True
                VL2.Text = dtr("INFO2").ToString
            End If
            VL1.Text = dtr("INFO1").ToString
        End If
        dtr.Close()
        dtr = Nothing
        cn.Open()

        da = New SqlDataAdapter("SELECT * FROM ZR WHERE TypeFLux='" & TypeF.SelectedValue & "' AND Site = '" & SiteZR.SelectedValue & "' AND Actif = 1", cn.getCn)
        If da.Fill(ds, "ZR") > 0 Then
            'recherche.Enabled = True
            'recherche.Text = Nothing
            Dim tmp As String = ds.Tables("ZR").Rows(0).Item("Choix").ToString
            With InfoC
                .DataSource = ds.Tables("ZR")
                If ds.Tables("ZR").Rows(0).Item("Choix").ToString = "Rayon" Then
                    .DisplayMember = "Rayon" 'ds.Tables("ZR").Columns(0).ColumnName
                    .ValueMember = "IdZR"
                    'InfoLabel.Text = "Rayon"
                Else
                    If tmp = "INFO1" Then
                        InfoC.DisplayMember = "INFO1_Doc"
                    Else
                        InfoC.DisplayMember = "INFO2_Doc"
                    End If
                    .ValueMember = "IdZR"
                    'InfoLabel.Text = ds.Tables("ZR").Rows(0).Item(ds.Tables("ZR").Rows(0).Item("Choix").ToString).ToString
                End If
                InfoC.Refresh()
            End With
            cn.Open()
            cmd = New SqlCommand("SELECT * FROM ZR WHERE TypeFLux='" & TypeF.SelectedValue & "' AND IdZR = " & InfoC.SelectedValue & " AND Actif = 1", cn.getCn)
            'val = cmd.ExecuteScalar
            dtr = cmd.ExecuteReader()
            dtr.Read()

            'If Not String.IsNullOrEmpty(dtr("INFO1_Doc")) Then
            'SiteZR.SelectedValue = dtr("Site").ToString
            'Else
            'SiteZR.SelectedValue = "00"
            'End If
            Rayon.Text = dtr("Rayon").ToString
            ZR.Text = dtr("ZR").ToString
            NewSite.SelectedValue = dtr("Site").ToString
            VV1.Text = dtr("INFO1_Doc").ToString
            VVC1.Text = dtr("INFO1_Eti").ToString
            VV2.Text = dtr("INFO2_Doc").ToString
            VVC2.Text = dtr("INFO2_Eti").ToString

            dtr.Close()
        End If
        

        Me.F_load = False
        cn.Close()
    End Sub

    Private Sub TypeF_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TypeF.SelectedIndexChanged
        If F_load Then
            Exit Sub
        End If

        Try
            ds.Tables("Sites").Clear()
        Catch ex As Exception

        End Try

        cn.Open()
        da = New SqlDataAdapter("SELECT Distinct Site,isnull(NomDoc,'Tous') as NomDoc FROM ZR left join Sites on NumSite = ZR.Site WHERE TypeFlux='" & TypeF.SelectedValue & "' AND  ZR.Actif = 1 AND Sites.Actif = 1", cn.getCn)
        ds = New DataSet
        da.Fill(ds, "Sites")
        With SiteZR
            .DataSource = ds.Tables("Sites")
            .DisplayMember = "NomDoc"
            .ValueMember = "Site"
            .SelectedIndex = 0
        End With

        cn.Open()
        cmd = New SqlCommand("SELECT TOP 1 * from ZR WHERE TypeFlux='" & TypeF.SelectedValue & "' AND Actif = 1", cn.getCn)
        'val = cmd.ExecuteScalar
        dtr = cmd.ExecuteReader()
        dtr.Read()

        CR.Text = dtr(dtr("Choix").ToString).ToString
        choix = dtr("Choix").ToString
        If String.IsNullOrEmpty(dtr("INFO2").ToString) Then
            Panel2.Visible = False
            VL2.Text = ""
            VV2.Text = ""
            VVC2.Text = ""
        Else
            Panel2.Visible = True
            VL2.Text = dtr("INFO2").ToString
        End If
        VL1.Text = dtr("INFO1").ToString
        dtr.Close()
        cn.Close()
    End Sub


    Private Sub Fermer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Fermer.Click
        Me.Close()
    End Sub

    Private Sub Enregistrer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Enregistrer.Click
        If String.IsNullOrEmpty(Rayon.Text) Then
            MsgBox("Veuillez saisir le rayon")
            Exit Sub
        ElseIf String.IsNullOrEmpty(ZR.Text) Then
            MsgBox("Veuillez saisir la zone de roulage")
            Exit Sub
        ElseIf choix = "INFO1" And (String.IsNullOrEmpty(VV1.Text) Or String.IsNullOrEmpty(VVC1.Text)) Then
            MsgBox("Veuillez saisir la valeurs 1")
            Exit Sub
        ElseIf choix = "INFO2" And (String.IsNullOrEmpty(VV2.Text) Or String.IsNullOrEmpty(VVC2.Text)) Then
            MsgBox("Veuillez saisir la valeurs 2")
            Exit Sub
        End If

        If choix = "Rayon" Then
            cn.Open()
            If NewSite.SelectedValue = "00" Then
                cmd = New SqlCommand("SELECT * from ZR WHERE TypeFlux='" & TypeF.SelectedValue & "' AND IdZR <> " & InfoC.SelectedValue & " AND Actif = 1 AND Rayon='" & Rayon.Text & "'", cn.getCn)
            Else
                cmd = New SqlCommand("SELECT * from ZR WHERE TypeFlux='" & TypeF.SelectedValue & "' AND IdZR <> " & InfoC.SelectedValue & " AND Site = '" & NewSite.SelectedValue & "' AND Actif = 1 AND Rayon='" & Rayon.Text & "'", cn.getCn)
            End If

            dtr = cmd.ExecuteReader()
            If dtr.HasRows Then
                MsgBox("Ce Rayon existe déjà")
                dtr.Close()
                Exit Sub
            End If
            dtr.Close()
            cn.Close()
        ElseIf choix = "INFO1" Then
            cn.Open()
            If NewSite.SelectedValue = "00" Then
                cmd = New SqlCommand("SELECT * from ZR WHERE TypeFlux='" & TypeF.SelectedValue & "' AND IdZR <> " & InfoC.SelectedValue & " AND Actif = 1 AND INFO1_Doc='" & VV1.Text & "'", cn.getCn)
            Else
                cmd = New SqlCommand("SELECT * from ZR WHERE TypeFlux='" & TypeF.SelectedValue & "' AND IdZR <> " & InfoC.SelectedValue & " AND Site = '" & NewSite.SelectedValue & "' AND Actif = 1 AND INFO1_Doc='" & VV1.Text & "'", cn.getCn)
            End If

            dtr = cmd.ExecuteReader()
            If dtr.HasRows Then
                MsgBox("La valeur 1 existe déjà")
                dtr.Close()
                Exit Sub
            End If
            dtr.Close()
            cn.Close()
        ElseIf choix = "INFO2" Then
            cn.Open()
            If NewSite.SelectedValue = "00" Then
                cmd = New SqlCommand("SELECT * from ZR WHERE TypeFlux='" & TypeF.SelectedValue & "' AND IdZR <> " & InfoC.SelectedValue & " AND Actif = 1 AND INFO2_Doc='" & VV2.Text & "'", cn.getCn)
            Else
                cmd = New SqlCommand("SELECT * from ZR WHERE TypeFlux='" & TypeF.SelectedValue & "' AND IdZR <> " & InfoC.SelectedValue & " AND Site = '" & NewSite.SelectedValue & "' AND Actif = 1 AND INFO2_Doc='" & VV2.Text & "'", cn.getCn)
            End If

            dtr = cmd.ExecuteReader()
            If dtr.HasRows Then
                MsgBox("La valeur 2 existe déjà")
                dtr.Close()
                Exit Sub
            End If
            dtr.Close()
            cn.Close()
        End If
        'dtr.Close()
        cn.Open()
        'cmd = New SqlCommand("UPDATE ZR SET Actif = 0 where IdZR = " & InfoC.SelectedValue, cn.getCn)
        'cmd.ExecuteNonQuery()

        cmd = New SqlCommand("UPDATE ZR SET Actif = 0 where IdZR = " & InfoC.SelectedValue, cn.getCn)
        cmd.ExecuteNonQuery()
        If NewSite.SelectedValue = "00" Then
            cmd = New SqlCommand("Insert into zr (TypeFlux,ZR,Rayon,INFO1,INFO1_Eti,INFO1_Doc,INFO2,INFO2_Eti,INFO2_Doc,Choix,Util,DateA,HeureA,Actif) " _
            & " values ('" & TypeF.SelectedValue & "','" & ZR.Text & "','" & Rayon.Text & "','" & VL1.Text & "','" & VVC1.Text & "','" & VV1.Text & "','" & VL2.Text & "','" & VVC2.Text & "','" & VV2.Text & "','" & choix & "','" & user & "','" & Date.Today & "','" & Date.Now.ToLongTimeString & "','true')", cn.getCn)
        Else
            cmd = New SqlCommand("Insert into zr (TypeFlux,ZR,Rayon,INFO1,INFO1_Eti,INFO1_Doc,INFO2,INFO2_Eti,INFO2_Doc,Choix,Site,Util,DateA,HeureA,Actif) " _
            & " values ('" & TypeF.SelectedValue & "','" & ZR.Text & "','" & Rayon.Text & "','" & VL1.Text & "','" & VVC1.Text & "','" & VV1.Text & "','" & VL2.Text & "','" & VVC2.Text & "','" & VV2.Text & "','" & choix & "'," & NewSite.SelectedValue & ",'" & user & "','" & Date.Today & "','" & Date.Now.ToLongTimeString & "','true')", cn.getCn)
        End If

        cmd.ExecuteNonQuery()
        MsgBox("Zone de roulage enregistrée")
        cn.Close()
        Me.Close()
    End Sub

    Private Sub InfoC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles InfoC.SelectedIndexChanged
        If F_load Then
            Exit Sub
        End If
        If InfoC.ValueMember = "" Then
            ZR.Text = ""
            'VL1.Text = ""
            VV1.Text = ""
            VVC1.Text = ""
            'VL2.Text = ""
            VV2.Text = ""
            VVC2.Text = ""
            Exit Sub
        End If
        If InfoC.DisplayMember = "" Then
            ZR.Text = ""
            'VL1.Text = ""
            VV1.Text = ""
            VVC1.Text = ""
            'VL2.Text = ""
            VV2.Text = ""
            VVC2.Text = ""
            Exit Sub
        End If
        If String.IsNullOrEmpty(InfoC.Text) Then
            ZR.Text = ""
            'VL1.Text = ""
            VV1.Text = ""
            VVC1.Text = ""
            'VL2.Text = ""
            VV2.Text = ""
            VVC2.Text = ""
            Exit Sub
        End If
        If String.IsNullOrEmpty(InfoC.SelectedValue) Then
            ZR.Text = ""
            'VL1.Text = ""
            VV1.Text = ""
            VVC1.Text = ""
            'VL2.Text = ""
            VV2.Text = ""
            VVC2.Text = ""
            Exit Sub
        End If
        Rayon.Text = ""
        ZR.Text = ""
        VV1.Text = ""
        VVC1.Text = ""
        VV2.Text = ""
        VVC2.Text = ""
        cn.Open()
        cmd = New SqlCommand("SELECT * FROM ZR WHERE TypeFLux='" & TypeF.SelectedValue & "' AND IdZR = " & InfoC.SelectedValue & " AND Actif = 1", cn.getCn)
        'val = cmd.ExecuteScalar

        cn.Open()
        dtr = cmd.ExecuteReader()
        dtr.Read()

        NewSite.SelectedValue = dtr("Site").ToString
        Rayon.Text = dtr("Rayon").ToString
        ZR.Text = dtr("ZR").ToString
        VV1.Text = dtr("INFO1_Doc").ToString
        VVC1.Text = dtr("INFO1_Eti").ToString
        VV2.Text = dtr("INFO2_Doc").ToString
        VVC2.Text = dtr("INFO2_Eti").ToString

        dtr.Close()
        cn.Close()
    End Sub

    Private Sub SiteZR_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SiteZR.SelectedIndexChanged
        If F_load Then
            Exit Sub
        End If

        Try
            ds.Tables("ZR").Clear()
        Catch ex As Exception

        End Try
        F_load = True
        cn.Open()
        da = New SqlDataAdapter("SELECT * FROM ZR WHERE TypeFLux='" & TypeF.SelectedValue & "'  AND Site = '" & SiteZR.SelectedValue & "' AND Actif = 1", cn.getCn)
        If da.Fill(ds, "ZR") > 0 Then
            'recherche.Enabled = True
            'recherche.Text = Nothing
            Dim tmp As String = ds.Tables("ZR").Rows(0).Item("Choix").ToString
            With InfoC
                .DataSource = ds.Tables("ZR")
                If ds.Tables("ZR").Rows(0).Item("Choix").ToString = "Rayon" Then
                    .DisplayMember = "Rayon" 'ds.Tables("ZR").Columns(0).ColumnName
                    .ValueMember = "IdZR"
                    'InfoLabel.Text = "Rayon"
                Else
                    If tmp = "INFO1" Then
                        InfoC.DisplayMember = "INFO1_Doc"
                    Else
                        InfoC.DisplayMember = "INFO2_Doc"
                    End If
                    .ValueMember = "IdZR"
                    'InfoLabel.Text = ds.Tables("ZR").Rows(0).Item(ds.Tables("ZR").Rows(0).Item("Choix").ToString).ToString
                End If
                InfoC.Refresh()
            End With
        End If
        F_load = False
        InfoC_SelectedIndexChanged(sender, e)
    End Sub

    Private Sub recherche_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles recherche.TextChanged
        Dim retrdvview As New DataView
        Dim a, b As String
        a = InfoC.DisplayMember
        b = InfoC.ValueMember
        With retrdvview
            .Table = ds.Tables("ZR")
            .RowFilter = InfoC.DisplayMember & " like '%" & recherche.Text & "%'"
        End With
        InfoC.ValueMember = ""
        InfoC.DataSource = retrdvview.ToTable()
        InfoC.DroppedDown = True
        Me.Cursor = Cursors.Default
        InfoC.DisplayMember = a
        InfoC.ValueMember = b
    End Sub

    Private Sub recherche_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles recherche.KeyPress
        If e.KeyChar = Chr(34) Or e.KeyChar = "'" Or e.KeyChar = "*" Then
            e.KeyChar = ""
            Exit Sub
        End If
    End Sub

End Class